package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.glowmee.cmd.FirstCategoryCMD;

/** 1차카테고리의 기능을 구현하는 클래스 */
public class FirstCategoryDAO {
	
	static Logger logger = Logger.getLogger(FirstCategoryDAO.class);
	
	private static FirstCategoryDAO instance = null;
	
	DBHandler db = null;
	
	public static FirstCategoryDAO getInstance() {
		if (instance == null) {
			instance = new FirstCategoryDAO();
		}
		return instance;
	}
	
	public FirstCategoryDAO() {
		db = DBHandler.getInstance();
	}

	/**
	 * 1차카테고리 추가
	 * @param firstCategoryText		- 1차카테고리 이름
	 * @param sortKey				- 정렬순서
	 * @param isDisplay				- 전시여부
	 * @return int					- 추가된 1차카테고리에 대한 일련번호를 리턴
	 */
	public int insertFirstCategory(FirstCategoryCMD cmd) throws Exception {
		logger.debug("[insertFirstCategory] ---------- Method Start ----------");
		logger.debug("[insertFirstCategory][PARAM] firstCategoryText=" + cmd.getFirstCategoryText());
		logger.debug("[insertFirstCategory][PARAM] sortKey=" + cmd.getSortKey());
		logger.debug("[insertFirstCategory][PARAM] isDisplay=" + cmd.getIsDisplay());
		
		int result = 0;
		
		String sql = "INSERT INTO firstcategory("
				+ "firstCategoryText, sortKey, isDisplay, create_date"
				+ ") VALUES (?, ?, ?, ?);";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getFirstCategoryText());
			pstmt.setInt(2, cmd.getSortKey());
			pstmt.setInt(3, cmd.getIsDisplay());
			pstmt.setString(4, DateTime.getInstance().getNowDateTimeString());
			
			DBHandler.getInstance().beginTrans();
			int count = pstmt.executeUpdate();
			
			if (count > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertFirstCategory] " + e.getLocalizedMessage());
			logger.error("[insertFirstCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertFirstCategory][RETURN] result=" + result);
		logger.debug("[insertFirstCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 1차카테고리 수정
	 * @param idFirstCategory		- 1차카테고리 항번
	 * @param firstCategoryText		- 1차카테고리 이름
	 * @param sortKey				- 정렬순서
	 * @param isDisplay				- 전시여부
	 * @return int					- 수정된 1차카테고리 갯수를 리턴
	 */
	public int updateFirstCategory(FirstCategoryCMD cmd) throws Exception {
		logger.debug("[updateFirstCategory] ---------- Method Start ----------");
		logger.debug("[updateFirstCategory][PARAM] idFirstCategory=" + cmd.getIdFirstCategory());
		logger.debug("[updateFirstCategory][PARAM] firstCategoryText=" + cmd.getFirstCategoryText());
		logger.debug("[updateFirstCategory][PARAM] sortKey=" + cmd.getSortKey());
		logger.debug("[updateFirstCategory][PARAM] isDisplay=" + cmd.getIsDisplay());
		
		int result = 0;
		
		String sql = "UPDATE firstcategory "
				+ "SET firstCategoryText=?, sortKey=?, isDisplay=?, modified_date=? "
				+ "WHERE idFirstCategory=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getFirstCategoryText());
			pstmt.setInt(2, cmd.getSortKey());
			pstmt.setInt(3, cmd.getIsDisplay());
			pstmt.setString(4, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(5, cmd.getIdFirstCategory());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateFirstCategory] " + e.getLocalizedMessage());
			logger.error("[updateFirstCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateFirstCategory][RETURN] result=" + result);
		logger.debug("[updateFirstCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 1차카테고리 삭제
	 * @param idFirstCategory		- 1차카테고리 항번
	 * @return int					- 삭제된 1차카테고리 갯수를리턴
	 */
	public int deleteFirstCategory(int idFirstCategory) throws Exception {
		logger.debug("[deleteFirstCategory] ---------- Method Start ----------");
		logger.debug("[deleteFirstCategory][PARAM] idFirstCategory=" + idFirstCategory);
		
		int result = 0;
		
		String sql = "DELETE FROM firstcategory "
				+ "WHERE idFirstCategory=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idFirstCategory);
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[deleteFirstCategory] " + e.getLocalizedMessage());
			logger.error("[deleteFirstCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[deleteFirstCategory][RETURN] result=" + result);
		logger.debug("[deleteFirstCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 1차카테고리 조회
	 * @param idFirstCategory		- 1차카테고리 항번
	 * @return FirstCategoryCMD		- 1차카테고리 객체로 리턴
	 */
	public FirstCategoryCMD getFirstCategory(int idFirstCategory) throws Exception {
		logger.debug("[getFirstCategory] ---------- Method Start ----------");
		logger.debug("[getFirstCategory][PARAM] idFirstCategory=" + idFirstCategory);
		
		FirstCategoryCMD result = null;
		String sql = "SELECT idFirstCategory, firstCategoryText, sortKey, isDisplay, create_date, modified_date "
				+ "FROM firstcategory WHERE idFirstCategory=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idFirstCategory);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					String firstCategoryText = rs.getString(2);
					int sortKey = rs.getInt(3);
					int isDisplay = rs.getInt(4);
					String create_date = rs.getString(5);
					String modified_date = rs.getString(6);
					result = new FirstCategoryCMD(num, firstCategoryText, sortKey, isDisplay, create_date, modified_date);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getFirstCategory] " + e.getLocalizedMessage());
			logger.error("[getFirstCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getFirstCategory][RETURN] result=" + result.toString());
		}
		logger.debug("[getFirstCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 1차카테고리 리스트 조회
	 * @param page		- 페이징처리를 위한 시작페이지번호
	 * @param count		- 페이징처리를 위한 한페이당 출력수
	 * @return			- 전체 1차카테고리를 ArrayList로 리턴
	 */
	public ArrayList<FirstCategoryCMD> getFirstCategoryList(int page, int count, String where) throws Exception {
		logger.debug("[getFirstCategoryList] ---------- Method Start ----------");
		logger.debug("[getFirstCategoryList][PARAM] page=" + page);
		logger.debug("[getFirstCategoryList][PARAM] count=" + count);
		logger.debug("[getFirstCategoryList][PARAM] where=" + where);
		
		ArrayList<FirstCategoryCMD> result = null;
		int cnt = db.getCount("firstcategory", "idFirstCategory", where);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql;
		if (where == null) {
			sql = "SELECT idFirstCategory, firstCategoryText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM firstcategory ORDER BY sortKey "
					+ "LIMIT ?, ?";
		} else {
			sql = "SELECT idFirstCategory, firstCategoryText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM firstcategory "
					+ "WHERE " + where + " "
					+ "ORDER BY sortKey LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<FirstCategoryCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					String firstCategoryText = rs.getString(2);
					int sortKey = rs.getInt(3);
					int isDisplay = rs.getInt(4);
					String create_date = rs.getString(5);
					String modified_date = rs.getString(6);
					FirstCategoryCMD item = new FirstCategoryCMD(num, firstCategoryText, sortKey, isDisplay, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getFirstCategoryList] " + e.getLocalizedMessage());
			logger.error("[getFirstCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getFirstCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getFirstCategoryList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<FirstCategoryCMD> getFirstCategoryList() throws Exception {
		logger.debug("[getFirstCategoryList] ---------- Method Start ----------");
		
		ArrayList<FirstCategoryCMD> result = null;
		String sql = "SELECT idFirstCategory, firstCategoryText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM firstcategory WHERE isDisplay=1 ORDER BY sortKey";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<FirstCategoryCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					String firstCategoryText = rs.getString(2);
					int sortKey = rs.getInt(3);
					int isDisplay = rs.getInt(4);
					String create_date = rs.getString(5);
					String modified_date = rs.getString(6);
					FirstCategoryCMD item = new FirstCategoryCMD(num, firstCategoryText, sortKey, isDisplay, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getFirstCategoryList] " + e.getLocalizedMessage());
			logger.error("[getFirstCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getFirstCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getFirstCategoryList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<String> getFirstCategoryXml() throws Exception {
		logger.debug("[getFirstCategoryList] ---------- Method Start ----------");
		
		ArrayList<String> result = null;
		String sql = "SELECT firstCategoryText "
					+ "FROM firstcategory WHERE isDisplay=1 ORDER BY sortKey";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<String>();
				
				while(rs.next()) {
					String firstCategoryText = rs.getString(1);
					result.add(firstCategoryText);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getFirstCategoryList] " + e.getLocalizedMessage());
			logger.error("[getFirstCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getFirstCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getFirstCategoryList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<String> getFirstCategoryXml(int idBrand) throws Exception {
		logger.debug("[getFirstCategoryList] ---------- Method Start ----------");
		
		ArrayList<String> result = null;
		String sql = "SELECT f.firstCategoryText "
					+ "FROM product p, secondcategorymapping m, secondcategory s, firstcategory f "
					+ "WHERE f.isDisplay=1 AND p.idProduct=m.idProduct AND m.idSecondCategory=s.idSecondCategory AND s.idFirstCategory=f.idFirstCategory "
					+ "AND p.idBrand=? GROUP BY f.idFirstCategory "
					+ "ORDER BY f.sortKey";
		
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idBrand);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<String>();
				
				while(rs.next()) {
					String firstCategoryText = rs.getString(1);
					result.add(firstCategoryText);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getFirstCategoryList] " + e.getLocalizedMessage());
			logger.error("[getFirstCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getFirstCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getFirstCategoryList] ---------- Method End ----------");
		
		return result;
	}
}
